package cn.edu.tsinghua.smxq.mobilecity.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.edu.tsinghua.smxq.mobilecity.po.ElectricityRecord;
import cn.edu.tsinghua.smxq.mobilecity.po.WaterRecord;
import cn.edu.tsinghua.smxq.mobilecity.utils.DBUtils;

public class WaterRecordDAO {
	
	public boolean create(WaterRecord waterRecord) throws SQLException {
		boolean success = false;
		Connection conn = DBUtils.Connect();
		PreparedStatement pstmt = conn
				.prepareStatement("INSERT INTO mobilecity.waterRecord(userid,cityid,month,usagewater,unitprice,rate,haspaid) values(?,?,?,?,?,?,?)");
		pstmt.setInt(1, waterRecord.getUserId());
		pstmt.setInt(2, waterRecord.getCityId());
		pstmt.setInt(3, waterRecord.getMonth());
		pstmt.setFloat(4, waterRecord.getUsage());
		pstmt.setFloat(5, waterRecord.getUnitPrice());
		pstmt.setFloat(6, waterRecord.getRate());
		pstmt.setInt(7, waterRecord.getHasPaid());
	
		if (pstmt.executeUpdate() == 1) {
			success = true;
		}
		pstmt.close();
		conn.close();
		return success;
	}
	
	public List<WaterRecord> getAllByUserId(int userId) throws SQLException {
		List<WaterRecord> waterRecordList = new ArrayList<WaterRecord>();
		Connection conn = DBUtils.Connect();
		ResultSet rs = null;
		PreparedStatement pstmt = conn
				.prepareStatement("SELECT * FROM mobilecity.waterrecord WHERE userid=?");
		pstmt.setInt(1, userId);
		rs = pstmt.executeQuery();
		WaterRecord waterRecord;
		while(rs.next()) {
			waterRecord = new WaterRecord();
			waterRecord.setId(rs.getInt("id"));
			waterRecord.setUserId(rs.getInt("userid"));
			waterRecord.setCityId(rs.getInt("cityid"));
			waterRecord.setMonth(rs.getInt("month"));
			waterRecord.setUsage(rs.getFloat("usagewater"));
			waterRecord.setRate(rs.getFloat("rate"));
			waterRecord.setUnitPrice(rs.getFloat("unitprice"));
			waterRecord.setHasPaid(rs.getInt("haspaid"));
			waterRecordList.add(waterRecord);
		}
		rs.close();
		pstmt.close();
		conn.close();
		return waterRecordList;
	}
	
	public WaterRecord getOneByUserId(int userId,int month) throws SQLException {
		WaterRecord waterRecord = null;
		Connection conn = DBUtils.Connect();
		ResultSet rs = null;
		PreparedStatement pstmt = conn
				.prepareStatement("SELECT * FROM mobilecity.waterrecord WHERE userid=? and month=?");
		pstmt.setInt(1, userId);
		pstmt.setInt(2, month);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			waterRecord = new WaterRecord();
			waterRecord.setId(rs.getInt("id"));
			waterRecord.setUserId(rs.getInt("userid"));
			waterRecord.setCityId(rs.getInt("cityid"));
			waterRecord.setMonth(rs.getInt("month"));
			waterRecord.setUsage(rs.getFloat("usagewater"));
			waterRecord.setRate(rs.getFloat("rate"));
			waterRecord.setUnitPrice(rs.getFloat("unitprice"));
			waterRecord.setHasPaid(rs.getInt("haspaid"));
		}
		rs.close();
		pstmt.close();
		conn.close();
		return waterRecord;
	}
	

	public boolean updateHasPaid(int id) throws SQLException {
		boolean success = false;
		Connection conn = DBUtils.Connect();
		PreparedStatement pstmt = conn
				.prepareStatement("UPDATE mobilecity.waterrecord SET haspaid=? WHERE id=?");
		pstmt.setInt(1, 1);
		pstmt.setInt(2, id);
	
		if (pstmt.executeUpdate() == 1) {
			success = true;
		}
		pstmt.close();
		conn.close();
		return success;
	}
	

}
